USE master;
GO

IF DATABASEPROPERTYEX (N'PerformanceDW', N'Version') IS NULL
BEGIN
	CREATE DATABASE PerformanceDW ON PRIMARY
	(NAME = N'PerformanceDW',
		FILENAME = N'C:\MSSQL\Data\PerformanceDW.mdf',
		SIZE = 512MB, FILEGROWTH = 256MB
	) LOG ON (NAME = N'PerformanceDW_log',
		FILENAME = N'C:\MSSQL\Data\PerformanceDW.ldf',
		SIZE = 64MB, FILEGROWTH = 32MB
	);

	ALTER DATABASE PerformanceDW SET RECOVERY SIMPLE;
END
GO

USE PerformanceDW;
GO

-- create the table to persist the wait stats
CREATE TABLE dbo.WaitStats
(
	row_num BIGINT NOT NULL,
	capture_date DATETIME NOT NULL,
	server_name SYSNAME NOT NULL,
	instance_name SYSNAME NOT NULL,
	sqlserver_startup_time DATETIME NOT NULL,
	wait_type NVARCHAR(120) NOT NULL,
	wait_time_s DECIMAL(14, 2) NOT NULL,
	resource_wait_time_s DECIMAL(14, 2) NOT NULL,
	signal_wait_time_s DECIMAL(14, 2) NOT NULL,
	wait_count BIGINT NOT NULL,
	wait_percentage DECIMAL(4, 2) NOT NULL,
	avg_wait_time_s DECIMAL(14, 2) NOT NULL,
	avg_resource_wait_time_s DECIMAL(14, 2) NOT NULL,
	avg_signal_wait_time_s DECIMAL(14, 2) NOT NULL
);
GO

-- create a unique clustered index on capture_date and row_num
CREATE UNIQUE CLUSTERED INDEX CI_WaitStats ON dbo.WaitStats
	(capture_date, row_num) ON [PRIMARY];

USE PerformanceDW;
GO

INSERT INTO PerformanceDW.dbo.WaitStats
(
	row_num,
	capture_date,
	server_name,
	instance_name,
	sqlserver_startup_time,
	wait_type,
	wait_time_s,
	resource_wait_time_s,
	signal_wait_time_s,
	wait_count,
	wait_percentage,
	avg_wait_time_s,
	avg_resource_wait_time_s,
	avg_signal_wait_time_s
)
EXECUTE
('
WITH Waits AS
	(SELECT
		wait_type,
		wait_time_ms / 1000.0 AS wait_time_s,
		(wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_time_s,
        signal_wait_time_ms / 1000.0 AS signal_wait_time_s,
		waiting_tasks_count AS wait_count,
		CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS decimal(12,2)) AS wait_percentage,
		ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS row_num
	FROM sys.dm_os_wait_stats WITH (NOLOCK)
	WHERE wait_type NOT IN (
        N''BROKER_EVENTHANDLER'', N''BROKER_RECEIVE_WAITFOR'', N''BROKER_TASK_STOP'',
		N''BROKER_TO_FLUSH'', N''BROKER_TRANSMITTER'', N''CHECKPOINT_QUEUE'',
        N''CHKPT'', N''CLR_AUTO_EVENT'', N''CLR_MANUAL_EVENT'', N''CLR_SEMAPHORE'', N''CXCONSUMER'',
        N''DBMIRROR_DBM_EVENT'', N''DBMIRROR_EVENTS_QUEUE'', N''DBMIRROR_WORKER_QUEUE'',
		N''DBMIRRORING_CMD'', N''DIRTY_PAGE_POLL'', N''DISPATCHER_QUEUE_SEMAPHORE'',
        N''EXECSYNC'', N''FSAGENT'', N''FT_IFTS_SCHEDULER_IDLE_WAIT'', N''FT_IFTSHC_MUTEX'',
        N''HADR_CLUSAPI_CALL'', N''HADR_FILESTREAM_IOMGR_IOCOMPLETION'', N''HADR_LOGCAPTURE_WAIT'', 
		N''HADR_NOTIFICATION_DEQUEUE'', N''HADR_TIMER_TASK'', N''HADR_WORK_QUEUE'',
        N''KSOURCE_WAKEUP'', N''LAZYWRITER_SLEEP'', N''LOGMGR_QUEUE'', 
		N''MEMORY_ALLOCATION_EXT'', N''ONDEMAND_TASK_QUEUE'',
		N''PARALLEL_REDO_DRAIN_WORKER'', N''PARALLEL_REDO_LOG_CACHE'', N''PARALLEL_REDO_TRAN_LIST'',
		N''PARALLEL_REDO_WORKER_SYNC'', N''PARALLEL_REDO_WORKER_WAIT_WORK'',
		N''PREEMPTIVE_HADR_LEASE_MECHANISM'', N''PREEMPTIVE_SP_SERVER_DIAGNOSTICS'',
		N''PREEMPTIVE_OS_LIBRARYOPS'', N''PREEMPTIVE_OS_COMOPS'', N''PREEMPTIVE_OS_CRYPTOPS'',
		N''PREEMPTIVE_OS_PIPEOPS'', N''PREEMPTIVE_OS_AUTHENTICATIONOPS'',
		N''PREEMPTIVE_OS_GENERICOPS'', N''PREEMPTIVE_OS_VERIFYTRUST'',
		N''PREEMPTIVE_OS_FILEOPS'', N''PREEMPTIVE_OS_DEVICEOPS'', N''PREEMPTIVE_OS_QUERYREGISTRY'',
		N''PREEMPTIVE_OS_WRITEFILE'',
		N''PREEMPTIVE_XE_CALLBACKEXECUTE'', N''PREEMPTIVE_XE_DISPATCHER'',
		N''PREEMPTIVE_XE_GETTARGETSTATE'', N''PREEMPTIVE_XE_SESSIONCOMMIT'',
		N''PREEMPTIVE_XE_TARGETINIT'', N''PREEMPTIVE_XE_TARGETFINALIZE'',
        N''PWAIT_ALL_COMPONENTS_INITIALIZED'', N''PWAIT_DIRECTLOGCONSUMER_GETNEXT'',
		N''QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'',
		N''QDS_ASYNC_QUEUE'',
        N''QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'', N''REQUEST_FOR_DEADLOCK_SEARCH'',
		N''RESOURCE_QUEUE'', N''SERVER_IDLE_CHECK'', N''SLEEP_BPOOL_FLUSH'', N''SLEEP_DBSTARTUP'',
		N''SLEEP_DCOMSTARTUP'', N''SLEEP_MASTERDBREADY'', N''SLEEP_MASTERMDREADY'',
        N''SLEEP_MASTERUPGRADED'', N''SLEEP_MSDBSTARTUP'', N''SLEEP_SYSTEMTASK'', N''SLEEP_TASK'',
        N''SLEEP_TEMPDBSTARTUP'', N''SNI_HTTP_ACCEPT'', N''SP_SERVER_DIAGNOSTICS_SLEEP'',
		N''SQLTRACE_BUFFER_FLUSH'', N''SQLTRACE_INCREMENTAL_FLUSH_SLEEP'', N''SQLTRACE_WAIT_ENTRIES'',
		N''WAIT_FOR_RESULTS'', N''WAITFOR'', N''WAITFOR_TASKSHUTDOWN'', N''WAIT_XTP_HOST_WAIT'',
		N''WAIT_XTP_OFFLINE_CKPT_NEW_LOG'', N''WAIT_XTP_CKPT_CLOSE'', N''WAIT_XTP_RECOVERY'',
		N''XE_BUFFERMGR_ALLPROCESSED_EVENT'', N''XE_DISPATCHER_JOIN'',
        N''XE_DISPATCHER_WAIT'', N''XE_LIVE_TARGET_TVF'', N''XE_TIMER_EVENT'')
     ),
RunningWaits AS (
	SELECT
		row_num,
		W1.wait_type,
		CAST(W1.wait_time_s AS DECIMAL(14, 2)) AS wait_time_s,
		CAST(W1.resource_wait_time_s AS DECIMAL(14, 2)) AS resource_wait_time_s,
		CAST(W1.signal_wait_time_s AS DECIMAL(14, 2)) AS signal_wait_time_s,
		W1.wait_count,
		CAST ((W1.wait_time_s / W1.wait_count) AS DECIMAL (14, 4)) AS avg_wait_time_s,
		CAST ((W1.resource_wait_time_s / W1.wait_count) AS DECIMAL (14, 4)) AS avg_resource_wait_time_s,
		CAST ((W1.signal_wait_time_s / W1.wait_count) AS DECIMAL (14, 4)) AS avg_signal_wait_time_s,
		W1.wait_percentage,
		SUM(W1.wait_percentage) OVER(ORDER BY W1.wait_percentage DESC ROWS UNBOUNDED PRECEDING) AS running_wait_percentage
	FROM Waits AS W1)
SELECT
	row_num, GETDATE() AS capture_date,
	@@SERVERNAME AS server_name, @@servicename AS instance_name, osi.sqlserver_start_time,
	wait_type, wait_time_s, resource_wait_time_s, signal_wait_time_s,
	wait_count, wait_percentage, avg_wait_time_s, avg_resource_wait_time_s, avg_signal_wait_time_s
FROM RunningWaits
CROSS APPLY (SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS osi
WHERE running_wait_percentage - wait_percentage <= 95 -- percentage threshold
ORDER BY running_wait_percentage ASC;'
);
GO

-- reviewing the last 30 days’ data
SELECT * FROM dbo.WaitStats
WHERE capture_date > DATEADD(day, -30, GETDATE())
ORDER BY row_num ASC;

-- reviewing the top wait for each collected data set
SELECT
	row_num,
	capture_date,
	wait_type,
	wait_percentage,
	wait_time_s,
	wait_count,
	avg_wait_time_s
FROM dbo.WaitStats
WHERE capture_date > DATEADD(day, -30, GETDATE())
AND row_num = 1
ORDER BY capture_date DESC;

-- cleanup
/*
USE master;
GO

IF DATABASEPROPERTYEX (N'PerformanceDW', N'Version') > 0
BEGIN
	ALTER DATABASE PerformanceDW SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE PerformanceDW;
END
*/